﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using EntryExit;


namespace EntryExit
{
    public class EmployeeManager
    {
        public ISqlDBManager DbManager { get; set; }
        private DataTable resultTable { get; set; }

        public EmployeeManager()
        {
            DbManager = new SqlDBManager();
        }

        public string GetLPNForWindowsUserName(string windowsUserName)
        {
            SqlParameter[] param = new SqlParameter[] { new SqlParameter() { ParameterName = "@WIN_USER_NAME", SqlDbType = SqlDbType.VarChar, Value = windowsUserName } };
            resultTable = DbManager.GetData(DBConstants.SP_AMS_GET_LPN_FOR_WINDOWS_UNAME, CommandType.StoredProcedure, DBName.SERVICE, param);
            return (resultTable == null || resultTable.Rows.Count == 0) ? string.Empty : Convert.ToString(resultTable.Rows[0][0]);
        }

        public Employee GetEmployee(string LPN)
        {
            SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter() { ParameterName = "@userLPN", SqlDbType = SqlDbType.VarChar, Value = LPN } };
            resultTable = DbManager.GetData(DBConstants.SP_AMS_GET_FULL_EMPLOYEE_DETAILS, CommandType.StoredProcedure, DBName.SERVICE, sqlParams);
            return (resultTable == null || resultTable.Rows.Count == 0) ? new Employee() : new Employee(resultTable.Rows[0]);
        }

        //public int GetCardHolderIdForLpn(string lpn)
        //{
        //    int cardHolderId = 0;
        //    SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter() { ParameterName = "@EMP_LPN", SqlDbType = SqlDbType.VarChar, Value = lpn } };
        //    resultTable = DbManager.GetData(DBConstants.SP_AMS_GET_CARD_HOLDER_ID, CommandType.StoredProcedure, DBName.CCFTCentral, sqlParams);
        //    if (resultTable != null && resultTable.Rows.Count > 0)
        //    {
        //        cardHolderId = Convert.ToInt32(resultTable.Rows[0][0]);
        //    }
        //    return cardHolderId;
        //}

        public DataTable GetWhoIsInInformation(string AuthorityLPN, DateTime StartDate, DateTime EndDate, ref string totalEmployees, ref string totalInEmployees)
        {
            //List<WhoIsIn> whoIsIn = new List<WhoIsIn>();
            SqlParameter[] parameters = new SqlParameter[5];


            parameters[0] = new SqlParameter("@StartDate", Common.FormatDate(StartDate));
            parameters[1] = new SqlParameter("@EndDate", Common.FormatDate(EndDate));
            parameters[2] = new SqlParameter("@SupervisorLPN", AuthorityLPN);
            parameters[3] = new SqlParameter("@TotalEmployees", totalEmployees);
            parameters[4] = new SqlParameter("@TotalInEmployees", totalInEmployees);

            parameters[3].Direction = ParameterDirection.Output;
            parameters[3].DbType = DbType.Int32;
            parameters[4].Direction = ParameterDirection.Output;
            parameters[4].DbType = DbType.Int32;

            resultTable = DbManager.GetData(DBConstants.SP_AMS_RPT_WHOISIN, CommandType.StoredProcedure, DBName.SERVICE, parameters);

            totalEmployees = Convert.ToString(parameters[3].Value);
            totalInEmployees = Convert.ToString(parameters[4].Value);

            return resultTable;
        }

        public List<Employee> GetSubEmployeesForAuthorityLPN(string AuthorityLPN)
        {
            //IList<Employee> subEmployees = new List<Employee>();
            //SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter() { ParameterName = "@supervisorLPN", SqlDbType = SqlDbType.VarChar, Value = AuthorityLPN } };
            //resultTable = DbManager.GetData(DBConstants.SP_AMS_GET_SUB_ORDINATES_WITH_CARDID, CommandType.StoredProcedure, DBName.SERVICE, sqlParams);
            //if (resultTable != null)
            //{
            //    foreach (DataRow dataRow in resultTable.Rows)
            //    {
            //        subEmployees.Add(new Employee(dataRow));
            //    }
            //}
            //return subEmployees;


            List<Employee> subEmployees = new List<Employee>();
            SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter() { ParameterName = "@supervisorLPN", SqlDbType = SqlDbType.VarChar, Value = AuthorityLPN } };
            resultTable = DbManager.GetData(DBConstants.SP_AMS_GET_SUB_ORDINATES_WITH_CARDID, CommandType.StoredProcedure, DBName.SERVICE, sqlParams);
            if (resultTable != null)
            {
                Employee subEmployee;
                foreach (DataRow dataRow in resultTable.Rows)
                {
                    subEmployee = new Employee();
                    //subEmployees.Add(new Employee(dataRow));


                    subEmployee.LPN = Convert.ToString(dataRow["LPN"]);
                    subEmployee.GPN = Convert.ToString(dataRow["GPN"]);
                    subEmployee.EmployeeName = Convert.ToString(dataRow["EmployeeName"]);
                    subEmployee.GradeName = Convert.ToString(dataRow["GradeName"]);
                    subEmployee.EmailAddress = Convert.ToString(dataRow["EmailAddress"]);
                    subEmployee.DepartmentName = Convert.ToString(dataRow["DepartmentName"]);
                    subEmployee.OfficeName = Convert.ToString(dataRow["OfficeName"]);
                    subEmployee.Country = Convert.ToString(dataRow["Country"]);
                    subEmployee.EmployeeStatus = (dataRow["EmployeeStatus"] != DBNull.Value) ? Convert.ToBoolean(dataRow["EmployeeStatus"]) : false;
                    subEmployee.DateOfBirth = (dataRow["DateOfBirth"] != DBNull.Value) ? Convert.ToDateTime(dataRow["DateOfBirth"]) : DateTime.MinValue;
                    subEmployee.JoiningDate = (dataRow["JoiningDate"] != DBNull.Value) ? Convert.ToDateTime(dataRow["JoiningDate"]) : DateTime.MinValue;
                    subEmployee.LeavingDate = (dataRow["LeavingDate"] != DBNull.Value) ? Convert.ToDateTime(dataRow["LeavingDate"]) : DateTime.MinValue;
                    subEmployee.EffectiveFrom = (dataRow["EffectiveFrom"] != DBNull.Value) ? Convert.ToDateTime(dataRow["EffectiveFrom"]) : DateTime.MinValue;
                    subEmployee.EffectiveTo = (dataRow["EffectiveTo"] != DBNull.Value) ? Convert.ToDateTime(dataRow["EffectiveTo"]) : DateTime.MinValue;
                    subEmployee.OfficeID = Convert.ToString(dataRow["OfficeID"]);
                    subEmployee.ServiceLineID = Convert.ToString(dataRow["ServiceLineID"]);
                    subEmployee.ServiceLineDescription = Convert.ToString(dataRow["ServiceLineDescription"]);
                    subEmployee.SubServiceLineID = Convert.ToString(dataRow["SubServiceLineID"]);
                    subEmployee.SubServiceLineDescription = Convert.ToString(dataRow["SubServiceLineDescription"]);
                    subEmployee.DepartmentID = Convert.ToString(dataRow["DepartmentID"]); ;
                    subEmployee.CodeBlockStatus = Convert.ToInt32(dataRow["CodeBlockStatus"]);
                    subEmployee.AuthorityLPN = Convert.ToString(dataRow["AuthorityLPN"]);
                    subEmployee.AuthorityName = Convert.ToString(dataRow["AuthorityName"]);
                    subEmployee.WindowsUserName = Convert.ToString(dataRow["WindowsUserName"]);
                    subEmployee.CardHolderId = Convert.ToInt32(dataRow["CardholderID"]);

                    subEmployee.Parent = (dataRow["Parent"] != DBNull.Value) ? Convert.ToString(dataRow["Parent"]) : string.Empty;
                    subEmployees.Add(subEmployee);
                }
            }
            return subEmployees;
        }


        public Dictionary<string, AttendanceInformation> GetEmployeeInTimeInfo(DateTime startDate, DateTime endDate, string LPN)
        {
            Dictionary<string, AttendanceInformation> attendanceDetails = new Dictionary<string, AttendanceInformation>();

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@START_DATE", Common.FormatDate(startDate));
            parameters[1] = new SqlParameter("@END_DATE", Common.FormatDate(endDate));
            parameters[2] = new SqlParameter("@LPN", LPN);

            resultTable = DbManager.GetData("[AMS_GetEmployeeEntryExitInfo]", CommandType.StoredProcedure, DBName.CCFTEvent, parameters);

            if (resultTable != null && resultTable.Rows.Count > 0)
            {
                foreach (DataRow row in resultTable.Rows)
                {
                    AttendanceInformation obj = new AttendanceInformation();
                    string eventDate = Convert.ToDateTime(row["TransactionDate"]).Date.ToShortDateString();
                    TimeSpan totalTime = DateTime.Parse(row["Timeinoffice"].ToString()).TimeOfDay;
                    obj.Time = totalTime;
                    obj.Info = row["Info"].ToString();

                    attendanceDetails.Add(eventDate, obj);

                }
            }

            return attendanceDetails;
        }

        // Created By   : Rajkumar.GS
        // Created Date    : 12/04/2014
        // Purpose : For Behaviour Page
        public void GetBehaviourReport(DateTime startDate, DateTime endDate, int cardHolderId
                , ref int veryLowHours, ref int lowHours, ref int normalHours, ref int overHours)
        {

            veryLowHours = 0;
            lowHours = 0;
            normalHours = 0;
            overHours = 0;

            SqlParameter[] parameters = new SqlParameter[6];
            parameters[0] = new SqlParameter("@START_DATE", Common.FormatDate(startDate));
            parameters[1] = new SqlParameter("@END_DATE", Common.FormatDate(endDate));
            parameters[2] = new SqlParameter("@FTITEM_ID", cardHolderId);
            parameters[3] = new SqlParameter("@NormalWorkHours", ConfigMgr.Instance.GetValue(ConfigKeys.CNFG_MIN_WORK_HOURS));
            parameters[4] = new SqlParameter("@OverWorkHours", ConfigMgr.Instance.GetValue(ConfigKeys.CNFG_MAX_WORK_HOURS));
            parameters[5] = new SqlParameter("@MinWorkHours", ConfigMgr.Instance.GetValue(ConfigKeys.CNFG_LOWEST_WORK_HOURS));

            resultTable = DbManager.GetData("AMS_GetEvents_Behaviour", CommandType.StoredProcedure, DBName.CCFTEvent, parameters);

            if (resultTable != null && resultTable.Rows.Count > 0)
            {
                veryLowHours = Convert.ToInt32(resultTable.Rows[0]["VeryLowIndex"]);
                lowHours = Convert.ToInt32(resultTable.Rows[0]["LowIndex"]);
                normalHours = Convert.ToInt32(resultTable.Rows[0]["NormalIndex"]);
                overHours = Convert.ToInt32(resultTable.Rows[0]["OverIndex"]);
            }
        }

        public Dictionary<string, TimeSpan> GetEmployeeAttendance(DateTime startDate, DateTime endDate, int cardHolderId)
        {
            Dictionary<string, TimeSpan> attendanceDetails = new Dictionary<string, TimeSpan>();
            Dictionary<string, List<CardEvent>> dateCardEvents = new Dictionary<string, List<CardEvent>>();

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@START_DATE", Common.FormatDate(startDate));
            parameters[1] = new SqlParameter("@END_DATE", Common.FormatDate(endDate));
            parameters[2] = new SqlParameter("@FTITEM_ID", cardHolderId);

            resultTable = DbManager.GetData(DBConstants.SP_AMS_GET_EVENTS, CommandType.StoredProcedure, DBName.CCFTEvent, parameters);

            if (resultTable != null && resultTable.Rows.Count > 0)
            {
                DateTime loopDateTimeVal;
                EventMode loopEventMode;
                string loopDateKeyValue = string.Empty;
                foreach (DataRow row in resultTable.Rows)
                {
                    loopDateTimeVal = Convert.ToDateTime(row["EventTime"]);
                    loopEventMode = (Convert.ToString(row["Action"]) == "ENTRY") ? EventMode.Entry : EventMode.Exit;
                    loopDateKeyValue = loopDateTimeVal.Date.ToShortDateString();
                    if (!dateCardEvents.ContainsKey(loopDateKeyValue))
                    {
                        dateCardEvents.Add(loopDateKeyValue, new List<CardEvent>() { new CardEvent() { EventTime = loopDateTimeVal, EventType = loopEventMode } });
                    }
                    else
                    {
                        dateCardEvents[loopDateKeyValue].Add(new CardEvent() { EventTime = loopDateTimeVal, EventType = loopEventMode });
                    }
                }
            }

            foreach (KeyValuePair<string, List<CardEvent>> dayEventCollection in dateCardEvents)
            {
                TimeSpan totalClockedTime = new TimeSpan(0, 0, 0);
                DateTime? loopInTime = null;
                DateTime? loopOutTime = null;
                foreach (CardEvent cardEvt in dayEventCollection.Value)
                {
                    if (cardEvt.EventType == EventMode.Entry)
                    {
                        loopInTime = cardEvt.EventTime;
                    }
                    else
                    {
                        loopOutTime = cardEvt.EventTime;
                    }
                    if (loopInTime != null && loopOutTime != null)
                    {
                        if (loopOutTime > loopInTime)
                        {
                            DateTime lo = (DateTime)loopOutTime;
                            DateTime li = (DateTime)loopInTime;
                            totalClockedTime = totalClockedTime.Add(lo.Subtract(li));
                            loopInTime = null;
                            loopOutTime = null;
                        }
                    }
                }
                attendanceDetails.Add(dayEventCollection.Key, totalClockedTime);
            }

            return attendanceDetails;
        }

        public Dictionary<string, TimeSpan> GetEmployeeAttendanceWithStatus(DateTime startDate, DateTime endDate, int cardHolderID, string userLPN)
        {
            Dictionary<string, TimeSpan> attendanceDetails = GetEmployeeAttendance(startDate, endDate, cardHolderID);
            Dictionary<string, int> shiftInfo = GetLeavesMarked(startDate, endDate, userLPN);
            List<string> unAuthDays = new List<string>();
            string loopDateString = "";
            for (DateTime i = startDate; i <= endDate; i = i.AddDays(1))
            {
                loopDateString = i.ToShortDateString();
                if (!attendanceDetails.ContainsKey(loopDateString))
                {
                    if (!shiftInfo.ContainsKey(loopDateString))
                    {
                        unAuthDays.Add(loopDateString);
                    }
                    else if (shiftInfo[loopDateString] == 1)
                    {
                        unAuthDays.Add(loopDateString);
                    }
                }
            }
            foreach (string day in unAuthDays)
            {
                attendanceDetails.Add(day, new TimeSpan(-1, 0, 0));
            }
            //foreach (KeyValuePair<string,TimeSpan> attendance in attendanceDetails)
            //{
            //    if(attendance.Value ==
            //}
            return attendanceDetails;
        }

        public Dictionary<string, int> GetEmployeeShiftAllocation(DateTime startDate, DateTime endDate, string employeeLPN)
        {
            Dictionary<string, int> shiftInfo = new Dictionary<string, int>();
            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter() { ParameterName = "@EMP_LPN", SqlDbType = SqlDbType.VarChar, Value = employeeLPN };
            sqlParams[1] = new SqlParameter() { ParameterName = "@START_DATE", SqlDbType = SqlDbType.VarChar, Value = Common.FormatDate(startDate) };
            sqlParams[2] = new SqlParameter() { ParameterName = "@END_DATE", SqlDbType = SqlDbType.VarChar, Value = Common.FormatDate(endDate) };

            resultTable = DbManager.GetData(DBConstants.SP_AMS_GET_SHIFT_DETAILS, CommandType.StoredProcedure, DBName.SERVICE, sqlParams);
            if (resultTable != null)
            {
                string loopShiftDate;
                int loopShiftId;
                foreach (DataRow row in resultTable.Rows)
                {
                    loopShiftDate = Convert.ToDateTime(row["ShiftDate"]).ToShortDateString();
                    loopShiftId = Convert.ToInt32(row["ShiftID"]);
                    if (!shiftInfo.ContainsKey(loopShiftDate))
                    {
                        shiftInfo.Add(loopShiftDate, loopShiftId);
                    }
                    else
                    {
                        shiftInfo[loopShiftDate] = loopShiftId;
                    }
                }
            }
            return shiftInfo;
        }

        public Dictionary<string, int> GetLeavesMarked(DateTime startDate, DateTime endDate, string employeeLPN)
        {
            Dictionary<string, int> statusInfo = new Dictionary<string, int>();
            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter() { ParameterName = "@EMP_LPN", SqlDbType = SqlDbType.VarChar, Value = employeeLPN };
            sqlParams[1] = new SqlParameter() { ParameterName = "@START_DATE", SqlDbType = SqlDbType.VarChar, Value = Common.FormatDate(startDate) };
            sqlParams[2] = new SqlParameter() { ParameterName = "@END_DATE", SqlDbType = SqlDbType.VarChar, Value = Common.FormatDate(endDate) };
            resultTable = DbManager.GetData(DBConstants.SP_AMS_GET_STATUS_DETAILS, CommandType.StoredProcedure, DBName.SERVICE, sqlParams);

            if (resultTable != null)
            {
                string loopStatusDate;
                int loopStatusId;
                foreach (DataRow row in resultTable.Rows)
                {
                    loopStatusDate = Convert.ToDateTime(row["ShiftDate"]).ToShortDateString();
                    loopStatusId = Convert.ToInt32(row["StatusID"]);
                    if (!statusInfo.ContainsKey(loopStatusDate))
                    {
                        statusInfo.Add(loopStatusDate, loopStatusId);
                    }
                    else
                    {
                        statusInfo[loopStatusDate] = loopStatusId;
                    }
                }
            }
            return statusInfo;
        }

        public LocationStatus GetLatestLocationStatus(DateTime startDate, DateTime endDate, int CardHolderId)
        {
            SqlParameter[] paramsArray = new SqlParameter[3];
            paramsArray[0] = new SqlParameter() { SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, ParameterName = "@START_DATE", Value = Common.FormatDate(startDate) };
            paramsArray[1] = new SqlParameter() { SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, ParameterName = "@END_DATE", Value = Common.FormatDate(endDate) };
            paramsArray[2] = new SqlParameter() { SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, ParameterName = "@CARD_HOLDER_ID", Value = CardHolderId };

            resultTable = DbManager.GetData(DBConstants.SP_AMS_GET_LATEST_EVENT_OF_EMPLOYEE, CommandType.StoredProcedure, DBName.CCFTEvent, paramsArray);
            if (resultTable != null && resultTable.Rows.Count > 0)
            {
                DataRow row = resultTable.Rows[0];
                return new LocationStatus() { LastStatusDateTime = Convert.ToDateTime(row["OccurrenceDateTime"]), ActionGate = Convert.ToString(row["GateName"]), Action = Convert.ToString(row["Action"]) };
            }
            else
            {
                return null;
            }
        }

        public DataTable GetLowWorkingEmployees(DateTime startDate, DateTime endDate, string selectedEmployees, int ReportType)
        {
            SqlParameter[] paramsArray = new SqlParameter[4];
            paramsArray[0] = new SqlParameter() { SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, ParameterName = "@START_DATE", Value = Common.FormatDate(startDate) };
            paramsArray[1] = new SqlParameter() { SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, ParameterName = "@END_DATE", Value = Common.FormatDate(endDate) };
            paramsArray[2] = new SqlParameter() { SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, ParameterName = "@userLPNs", Value = selectedEmployees };
            paramsArray[3] = new SqlParameter() { SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, ParameterName = "@ReportType", Value = ReportType };

            return DbManager.GetData(DBConstants.SP_AMS_RPT_LOWWORKHOURS, CommandType.StoredProcedure, DBName.CCFTEvent, paramsArray);

            //if (resultTable != null && resultTable.Rows.Count > 0)
            //{
            //    DataRow row = resultTable.Rows[0];
            //    return new LocationStatus() { LastStatusDateTime = Convert.ToDateTime(row["OccurrenceDateTime"]), ActionGate = Convert.ToString(row["GateName"]), Action = Convert.ToString(row["Action"]) };
            //}
            //else
            //{
            //    return null;
            //}
        }
        public Dictionary<string, DateTime> GetEmployeeFirstSwipe(DateTime startDate, DateTime endDate, int cardHolderId)
        {
            Dictionary<string, DateTime> attendanceDetails = new Dictionary<string, DateTime>();
            Dictionary<string, List<CardEvent>> dateCardEvents = new Dictionary<string, List<CardEvent>>();

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@START_DATE", Common.FormatDate(startDate));
            parameters[1] = new SqlParameter("@END_DATE", Common.FormatDate(endDate));
            parameters[2] = new SqlParameter("@FTITEM_ID", cardHolderId);
            resultTable = DbManager.GetData(DBConstants.SP_AMS_GET_EVENTS, CommandType.StoredProcedure, DBName.CCFTEvent, parameters);

            if (resultTable != null && resultTable.Rows.Count > 0)
            {
                DateTime loopDateTimeVal;
                //EventMode loopEventMode;
                string loopDateKeyValue = string.Empty;
                foreach (DataRow row in resultTable.Rows)
                {
                    loopDateTimeVal = Convert.ToDateTime(row["EventTime"]);
                    loopDateKeyValue = loopDateTimeVal.Date.ToShortDateString();
                    if ((Convert.ToString(row["Action"]) == "ENTRY") && (resultTable.Rows.IndexOf(row) == 0))
                    {
                        attendanceDetails.Add(loopDateTimeVal.ToShortDateString(), loopDateTimeVal);
                    }
                    else if (((Convert.ToString(row["Action"]) == "ENTRY") && (Convert.ToDateTime(resultTable.Rows[resultTable.Rows.IndexOf(row) - 1]["EventTime"]).ToShortDateString() != loopDateTimeVal.ToShortDateString())))
                    {
                        attendanceDetails.Add(loopDateTimeVal.ToShortDateString(), loopDateTimeVal);
                    }
                    else if ((!attendanceDetails.ContainsKey(loopDateTimeVal.ToShortDateString())) && (Convert.ToString(row["Action"]) == "ENTRY"))
                    {
                        attendanceDetails.Add(loopDateTimeVal.ToShortDateString(), loopDateTimeVal);
                    }

                    //loopEventMode = (Convert.ToString(row["Action"]) == "ENTRY") ? EventMode.Entry : EventMode.Exit;

                    //if (!dateCardEvents.ContainsKey(loopDateKeyValue))
                    //{
                    //    dateCardEvents.Add(loopDateKeyValue, new List<CardEvent>() { new CardEvent() { EventTime = loopDateTimeVal, EventType = loopEventMode } });
                    //}
                    //else
                    //{
                    //    dateCardEvents[loopDateKeyValue].Add(new CardEvent() { EventTime = loopDateTimeVal, EventType = loopEventMode });
                    //}
                }
            }

            //foreach (KeyValuePair<string, List<CardEvent>> dayEventCollection in dateCardEvents)
            //{
            //    foreach (CardEvent cardEvt in dayEventCollection.Value)
            //    {
            //        if (cardEvt.EventType == EventMode.Entry)
            //        {
            //            attendanceDetails.Add(dayEventCollection.Key, cardEvt.EventTime);
            //            break;
            //        }

            //    }
            //}

            return attendanceDetails;
        }

        public Dictionary<string, DateTime> GetEmployeeLastSwipe(DateTime startDate, DateTime endDate, int cardHolderId)
        {

            Dictionary<string, DateTime> attendanceDetails = new Dictionary<string, DateTime>();
            Dictionary<string, List<CardEvent>> dateCardEvents = new Dictionary<string, List<CardEvent>>();

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@START_DATE", Common.FormatDate(startDate));
            parameters[1] = new SqlParameter("@END_DATE", Common.FormatDate(endDate));
            parameters[2] = new SqlParameter("@FTITEM_ID", cardHolderId);
            resultTable = DbManager.GetData(DBConstants.SP_AMS_GET_EVENTS, CommandType.StoredProcedure, DBName.CCFTEvent, parameters);

            if (resultTable != null && resultTable.Rows.Count > 0)
            {

                DateTime loopDateTimeVal;
                //EventMode loopEventMode;
                string loopDateKeyValue = string.Empty;
                foreach (DataRow row in resultTable.Rows)
                {
                    loopDateTimeVal = Convert.ToDateTime(row["EventTime"]);
                    loopDateKeyValue = loopDateTimeVal.Date.ToShortDateString();
                    if ((Convert.ToString(row["Action"]) == "EXIT") && (resultTable.Rows.IndexOf(row) == resultTable.Rows.Count - 1))
                    {
                        attendanceDetails.Add(loopDateTimeVal.ToShortDateString(), loopDateTimeVal);
                    }
                    else if (((Convert.ToString(row["Action"]) == "EXIT") && (Convert.ToDateTime(resultTable.Rows[resultTable.Rows.IndexOf(row) + 1]["EventTime"]).ToShortDateString() != loopDateTimeVal.ToShortDateString())))
                    {
                        attendanceDetails.Add(loopDateTimeVal.ToShortDateString(), loopDateTimeVal);
                    }
                    //loopDateTimeVal = Convert.ToDateTime(row["EventTime"]);
                    //loopEventMode = (Convert.ToString(row["Action"]) == "EXIT") ? EventMode.Entry : EventMode.Exit;
                    //loopDateKeyValue = loopDateTimeVal.Date.ToShortDateString();
                    //if (!dateCardEvents.ContainsKey(loopDateKeyValue))
                    //{
                    //    dateCardEvents.Add(loopDateKeyValue, new List<CardEvent>() { new CardEvent() { EventTime = loopDateTimeVal, EventType = loopEventMode } });
                    //}
                    //else
                    //{
                    //    dateCardEvents[loopDateKeyValue].Add(new CardEvent() { EventTime = loopDateTimeVal, EventType = loopEventMode });
                    //}
                }
            }

            //foreach (KeyValuePair<string, List<CardEvent>> dayEventCollection in dateCardEvents)
            //{
            //    foreach (CardEvent cardEvt in dayEventCollection.Value)
            //    {
            //        if (cardEvt.EventType == EventMode.Entry)
            //        {
            //            attendanceDetails.Add(dayEventCollection.Key, cardEvt.EventTime);
            //            break;
            //        }

            //    }
            //}

            return attendanceDetails;
        }

        #region Base Code


        //internal EmployeeTree GetEmployeeTreeData(Employee rootUser)
        //{
        //    EmployeeTree empTreeData = new EmployeeTree()
        //    {
        //        Employee = rootUser,
        //        SubEmployees = new List<EmployeeTree>()
        //    };
        //    FillSubEmployeeTreeData(empTreeData);
        //    return empTreeData;
        //}

        //private void FillSubEmployeeTreeData(EmployeeTree resultingEmpTreeData)
        //{
        //    IList<Employee> employeeList = GetSubEmployeesForAuthorityLPN(resultingEmpTreeData.Employee.LPN);
        //    if (employeeList != null && employeeList.Count > 0)
        //    {
        //        foreach (Employee employee in employeeList)
        //        {
        //            resultingEmpTreeData.
        //                SubEmployees.Add
        //                (
        //                new EmployeeTree()
        //                {
        //                    Employee = employee,
        //                    SubEmployees = new List<EmployeeTree>()
        //                }
        //                );
        //        }

        //        foreach (EmployeeTree childNode in resultingEmpTreeData.SubEmployees)
        //        {
        //            FillSubEmployeeTreeData(childNode);
        //        }
        //    }
        //}

        //TO DO : modified code to fetch all employees with their parent

        #endregion

        internal EmployeeTree GetEmployeeTreeData(Employee rootUser)
        {
            EmployeeTree empTreeData = new EmployeeTree()
            {
                Employee = rootUser,
                SubEmployees = new List<EmployeeTree>()
            };
            List<Employee> employeeList = GetSubEmployeesForAuthorityLPN(empTreeData.Employee.LPN);
            FillSubEmployeeTreeData(empTreeData, employeeList);
            return empTreeData;
        }

        private void FillSubEmployeeTreeData(EmployeeTree resultingEmpTreeData, List<Employee> employeeList)
        {

            List<Employee> Employees = new List<Employee>();
            Employees = employeeList.FindAll(Employee => Employee.AuthorityLPN == resultingEmpTreeData.Employee.LPN);

            if (Employees != null && employeeList.Count > 0)
            {
                foreach (Employee employee in Employees)
                {

                    resultingEmpTreeData.
                        SubEmployees.Add
                        (
                        new EmployeeTree()
                        {
                            Employee = employee,
                            SubEmployees = new List<EmployeeTree>()
                        }
                        );
                }

                foreach (EmployeeTree childNode in resultingEmpTreeData.SubEmployees)
                {
                    FillSubEmployeeTreeData(childNode, employeeList);
                }
            }
        }

        /// <summary>
        /// This function is for temporary purpose. 
        /// Created a page to compare the attendence calanders, 
        /// This function will supply the attendence details for calendar from the Event table directly
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="cardHolderID"></param>
        /// <param name="userLPN"></param>
        /// <returns></returns>
        internal Dictionary<string, AttendanceInfo> GetEmployeeAttendanceWithLeave_Original(DateTime startDate, DateTime endDate,
          int cardHolderID, string userLPN)
        {
            Dictionary<string, AttendanceInfo> result = new Dictionary<string, AttendanceInfo>();
            TimeSpan configuredMinWorkHours = new TimeSpan(Convert.ToInt32(ConfigMgr.Instance.
                         GetValue(ConfigKeys.CNFG_MIN_WORK_HOURS)), 0, 0);
            TimeSpan configuredMaxWorkHours = new TimeSpan(Convert.ToInt32(ConfigMgr.Instance.
                        GetValue(ConfigKeys.CNFG_MAX_WORK_HOURS)), 0, 0);

            TimeSpan configuredLowestWorkHours = new TimeSpan(Convert.ToInt32(ConfigMgr.Instance.
                       GetValue(ConfigKeys.CNFG_LOWEST_WORK_HOURS)), 0, 0);

            Dictionary<string, TimeSpan> attendanceDetails = GetEmployeeAttendance(startDate, endDate, cardHolderID);

            Dictionary<string, int> leaveStatus = GetLeavesMarked(startDate, endDate, userLPN);

            string loopDateString = "";
            for (DateTime day = startDate; day <= endDate; day = day.AddDays(1))
            {
                loopDateString = day.ToShortDateString();
                AttendanceInfo info;

                if (!attendanceDetails.ContainsKey(loopDateString))
                {
                    if (!leaveStatus.ContainsKey(loopDateString))
                    {
                        info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_NO_ATTENDANCE_DATA_NO_LEAVE_DATA };
                    }
                    else
                    {
                        LeaveLegend leaveId = (LeaveLegend)leaveStatus[loopDateString];
                        if (leaveId == LeaveLegend.Working)
                        {
                            info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_NO_ATTENDANCE_DATA_NO_LEAVE_DATA };
                        }
                        else
                        {
                            string leaveInfo = GetLeaveInfo(leaveId);
                            info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_MARKED_AS_LEAVE, LeaveInfo = leaveInfo };
                        }
                    }
                }
                else
                {
                    TimeSpan time = attendanceDetails[loopDateString];
                    if (time < configuredMinWorkHours && time > configuredLowestWorkHours)
                    {
                        info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_LOW_WORK_HOURS, TotalInTime = time };
                    }
                    else if (time < configuredLowestWorkHours)
                    {
                        info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_LESSTHAN3_WORK_HOURS, TotalInTime = time };
                    }
                    else if (time > configuredMaxWorkHours)
                    {
                        info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_OVER_WORK_HOURS, TotalInTime = time };
                    }
                    else
                    {
                        info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_NORMAL_WORK_HOURS, TotalInTime = time };
                    }
                }
                result.Add(loopDateString, info);
            }
            return result;
        }

        internal Dictionary<string, AttendanceInfo> GetEmployeeAttendanceWithLeave(DateTime startDate, DateTime endDate,
            int cardHolderID, string userLPN)
        {
            Dictionary<string, AttendanceInfo> result = new Dictionary<string, AttendanceInfo>();
            TimeSpan configuredMinWorkHours = new TimeSpan(Convert.ToInt32(ConfigMgr.Instance.
                         GetValue(ConfigKeys.CNFG_MIN_WORK_HOURS)), 0, 0);
            TimeSpan configuredMaxWorkHours = new TimeSpan(Convert.ToInt32(ConfigMgr.Instance.
                        GetValue(ConfigKeys.CNFG_MAX_WORK_HOURS)), 0, 0);

            TimeSpan configuredLowestWorkHours = new TimeSpan(Convert.ToInt32(ConfigMgr.Instance.
                       GetValue(ConfigKeys.CNFG_LOWEST_WORK_HOURS)), 0, 0);

            //Dictionary<string, TimeSpan> attendanceDetails = GetEmployeeAttendance(startDate, endDate, cardHolderID);
            Dictionary<string, AttendanceInformation> attendanceDetails = GetEmployeeInTimeInfo(startDate, endDate, userLPN);
            Dictionary<string, int> leaveStatus = GetLeavesMarked(startDate, endDate, userLPN);

            string loopDateString = "";
            for (DateTime day = startDate; day <= endDate; day = day.AddDays(1))
            {
                loopDateString = day.ToShortDateString();
                AttendanceInfo info = new AttendanceInfo();
                LeaveLegend leaveId = new LeaveLegend();
                string leaveInfo = string.Empty;
                int StatusId = 0;

                /*updating the leave status*/
                if (leaveStatus.ContainsKey(loopDateString))
                {
                    leaveId = (LeaveLegend)leaveStatus[loopDateString];
                    leaveInfo = GetLeaveInfo(leaveId);
                    if (leaveId == LeaveLegend.CasualLeaveHalfDay || leaveId == LeaveLegend.SickLeaveHalfDay)
                    {
                        StatusId = UIConstants.IDENTIFIER_LOW_WORK_HOURS_HalfDay;
                    }
                    else if (leaveId == LeaveLegend.PublicHoliday)
                    {

                        StatusId = UIConstants.IDENTIFIER_NO_LEAVE_DATA_AVAILABLE_SUN_OR_SAT;
                    }
                    else if (leaveId == LeaveLegend.OffDay)
                    {

                        StatusId = UIConstants.IDENTIFIER_NO_DATA_OFFDAY;
                    }
                    else
                    {
                        StatusId = UIConstants.IDENTIFIER_MARKED_AS_LEAVE;
                    }

                }


                if (!attendanceDetails.ContainsKey(loopDateString))
                {
                    /*updating the attendance info - Time entry not found */
                    if (!leaveStatus.ContainsKey(loopDateString))
                    {
                        info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_NO_DATA_OFFDAY };
                    }
                    else
                    {
                        if (leaveId == LeaveLegend.Working)
                        {
                            info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_NO_ATTENDANCE_DATA_NO_LEAVE_DATA };
                        }
                        else
                        {
                            info = new AttendanceInfo() { StatusId = StatusId, LeaveInfo = leaveInfo };
                        }
                    }
                }
                else
                {
                    /*updating the attendance info - Time entry not */
                    AttendanceInformation obj=attendanceDetails[loopDateString];
                    TimeSpan time = obj.Time;
                    //if (leaveStatus.ContainsKey(loopDateString))
                    //{
                    //    info = new AttendanceInfo() { StatusId = StatusId, LeaveInfo = leaveInfo, TotalInTime = time };
                    //}
                    //else
                    //{
                    if (leaveId == LeaveLegend.CasualLeaveHalfDay || leaveId == LeaveLegend.SickLeaveHalfDay)
                    {
                        info = new AttendanceInfo() { StatusId = StatusId, LeaveInfo = leaveInfo, TotalInTime = time, Info = obj.Info };
                    }
                    else if (time < configuredMinWorkHours && time > configuredLowestWorkHours)
                    {
                        info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_LOW_WORK_HOURS, TotalInTime = time, Info = obj.Info };
                    }
                    else if (time < configuredLowestWorkHours)
                    {
                        info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_LESSTHAN3_WORK_HOURS, TotalInTime = time, Info = obj.Info };
                    }
                    else if (time > configuredMaxWorkHours)
                    {
                        info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_OVER_WORK_HOURS, TotalInTime = time, Info = obj.Info };
                    }
                    else
                    {
                        info = new AttendanceInfo() { StatusId = UIConstants.IDENTIFIER_NORMAL_WORK_HOURS, TotalInTime = time, Info = obj.Info };
                    }


                }
                result.Add(loopDateString, info);
            }
            return result;
        }

        internal string GetLeaveInfo(LeaveLegend LeaveId)
        {
            string leaveInfo = string.Empty;
            switch (LeaveId)
            {
                case LeaveLegend.Working:
                    leaveInfo = "Working";
                    break;
                case LeaveLegend.OffDay:
                    leaveInfo = "Off Day";
                    break;
                case LeaveLegend.PublicHoliday:
                    leaveInfo = "Public Holiday";
                    break;
                case LeaveLegend.CompensatoryOff:
                    leaveInfo = "Compensatory Off";
                    break;
                case LeaveLegend.SickLeave:
                    leaveInfo = "Sick Leave";
                    break;
                case LeaveLegend.OptionalHoliday:
                    leaveInfo = "Optional Holiday";
                    break;
                case LeaveLegend.CasualLeave:
                    leaveInfo = "Casual Leave";
                    break;
                case LeaveLegend.LossOfPay:
                    leaveInfo = "Loss Of Pay";
                    break;
                case LeaveLegend.PaternityLeave:
                    leaveInfo = "Paternity Leave";
                    break;
                case LeaveLegend.MaternityLeave:
                    leaveInfo = "Maternity Leave";
                    break;

                //To Do - Handle case 11 I don't have any info with these leaves

                case LeaveLegend.StudyLeave:
                    leaveInfo = "Study Leave";
                    break;
                case LeaveLegend.CompensatoryOffEligible:
                    leaveInfo = "Compensatory Off Eligible";
                    break;
                case LeaveLegend.CasualLeaveHalfDay:
                    leaveInfo = "Casual Leave Half Day";
                    break;
                case LeaveLegend.SickLeaveHalfDay:
                    leaveInfo = "Sick Leave Half Day";
                    break;

                //To Do - Handle case 16,17,18,19,20 I don't have any info with these leaves

                case LeaveLegend.OptionalHolidayLeave:
                    leaveInfo = "Optional Holiday (Leave)";
                    break;
                default:
                    leaveInfo = "Undefined Leave";
                    break;
            }
            return leaveInfo;
        }
        public DataTable GetSubEmployeesForAuthorityLPNVal(string AuthorityLPN)
        {
            List<Employee> subEmployees = new List<Employee>();
            SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter() { ParameterName = "@supervisorLPN", SqlDbType = SqlDbType.VarChar, Value = AuthorityLPN } };
            resultTable = DbManager.GetData(DBConstants.SP_AMS_GET_SUB_ORDINATES_WITH_CARDID, CommandType.StoredProcedure, DBName.SERVICE, sqlParams);

            return resultTable;
        }
        public DataTable GetEmployeeDetailReport(DateTime startDate, DateTime endDate, int cardHolderId)
        {

            Dictionary<string, DateTime> attendanceDetails = new Dictionary<string, DateTime>();
            Dictionary<string, List<CardEvent>> dateCardEvents = new Dictionary<string, List<CardEvent>>();

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@START_DATE", Common.FormatDate(startDate));
            parameters[1] = new SqlParameter("@END_DATE", Common.FormatDate(endDate));
            parameters[2] = new SqlParameter("@FTITEM_ID", cardHolderId);
            resultTable = DbManager.GetData("AMS_GetEvents_Detail", CommandType.StoredProcedure, DBName.CCFTEvent, parameters);



            return resultTable;
        }
        public DataTable GetDepartments()
        {
            SqlParameter[] sqlParams = null;
            resultTable = DbManager.GetData(DBConstants.SP_AMS_RPT_getDepartments, CommandType.StoredProcedure, DBName.SERVICE, sqlParams);
            return resultTable;
        }
        public DataTable GetEmployees(string deptid)
        {
            SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter() { ParameterName = "@DepartmentID", SqlDbType = SqlDbType.VarChar, Value = deptid } };
            resultTable = DbManager.GetData(DBConstants.SP_AMS_RPT_getEmployees, CommandType.StoredProcedure, DBName.SERVICE, sqlParams);
            return resultTable;
        }

        public DataTable UpdateTempCartUseage(string LPN, DateTime InDateTime, DateTime OutDateTime, TimeSpan Timeinoffice, string TempCardNo, string CreatedByLPN)
        {
            SqlParameter[] parameters = new SqlParameter[7];
            parameters[0] = new SqlParameter() { ParameterName = "@LPN", SqlDbType = SqlDbType.Int, Value = LPN };
            parameters[1] = new SqlParameter() { ParameterName = "@InDateTime", SqlDbType = SqlDbType.DateTime, Value = InDateTime };
            parameters[2] = new SqlParameter() { ParameterName = "@OutDateTime", SqlDbType = SqlDbType.DateTime, Value = OutDateTime };
            parameters[3] = new SqlParameter() { ParameterName = "@ShiftDate", SqlDbType = SqlDbType.Date, Value = InDateTime };
            parameters[4] = new SqlParameter() { ParameterName = "@Timeinoffice", SqlDbType = SqlDbType.Time, Value = Timeinoffice };
            parameters[5] = new SqlParameter() { ParameterName = "@TempCardNo", SqlDbType = SqlDbType.VarChar, Value = TempCardNo };
            parameters[6] = new SqlParameter() { ParameterName = "@CreatedByLPN", SqlDbType = SqlDbType.VarChar, Value = CreatedByLPN };
            DataSet resultTable = DbManager.GetData(DBConstants.SP_AMS_RPT_UpdateTempCardInfo, CommandType.StoredProcedure, DBName.SERVICE, parameters, "DS");
            return resultTable.Tables[0];
        }
        public Dictionary<string, DateTime> GetFirstSwipe(DateTime startDate, DateTime endDate, int cardHolderId,bool exitentry)
        {
            Dictionary<string, DateTime> attendanceDetails = new Dictionary<string, DateTime>();
            Dictionary<string, List<CardEvent>> dateCardEvents = new Dictionary<string, List<CardEvent>>();

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@START_DATE", Common.FormatDate(startDate));
            parameters[1] = new SqlParameter("@END_DATE", Common.FormatDate(endDate));
            parameters[2] = new SqlParameter("@FTITEM_ID", cardHolderId);
            resultTable = DbManager.GetData("[AMS_GetEvents1]", CommandType.StoredProcedure, DBName.CCFTEvent, parameters);

            if (resultTable != null && resultTable.Rows.Count > 0)
            {
                DateTime loopDateTimeVal;
                DateTime loopDateTimeKeyVal;
                string loopDateKeyValue = string.Empty;
                foreach (DataRow row in resultTable.Rows)
                {
                    if(exitentry==true)
                    loopDateTimeVal = Convert.ToDateTime(row["EntryTime"]);
                    else
                        loopDateTimeVal = Convert.ToDateTime(row["ExitTime"]);

                    loopDateTimeKeyVal = Convert.ToDateTime(row["ShiftDate"]);

                    loopDateKeyValue = loopDateTimeKeyVal.Date.ToString("ddMMMyyyy");
                    attendanceDetails.Add(loopDateTimeKeyVal.ToShortDateString(), loopDateTimeVal);
                }
            }

            return attendanceDetails;
        }
        public Dictionary<string, TimeSpan> GetAttendance(DateTime startDate, DateTime endDate, int cardHolderId)
        {
            Dictionary<string, TimeSpan> attendanceDetails = new Dictionary<string, TimeSpan>();

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@START_DATE", Common.FormatDate(startDate));
            parameters[1] = new SqlParameter("@END_DATE", Common.FormatDate(endDate));
            parameters[2] = new SqlParameter("@FTITEM_ID", cardHolderId);

            resultTable = DbManager.GetData("AMS_GetEvents1", CommandType.StoredProcedure, DBName.CCFTEvent, parameters);

            if (resultTable != null && resultTable.Rows.Count > 0)
            {
                DateTime loopDateTimeVal;
                DateTime loopToDateTimeVal;
                DateTime loopDateTimeKeyVal;
                string loopDateKeyValue = string.Empty;
                foreach (DataRow row in resultTable.Rows)
                {
                    loopDateTimeVal = Convert.ToDateTime(row["EntryTime"]);
                    loopToDateTimeVal = (Convert.ToDateTime(row["ExitTime"]));
                    loopDateTimeKeyVal = Convert.ToDateTime(row["ShiftDate"]);
                    loopDateKeyValue = loopDateTimeKeyVal.Date.ToShortDateString();
                    if (!attendanceDetails.ContainsKey(loopDateKeyValue))
                    {
                        attendanceDetails.Add(loopDateKeyValue, loopToDateTimeVal - loopDateTimeVal);
                    }
                    else
                    {
                        attendanceDetails[loopDateKeyValue].Add( loopToDateTimeVal - loopDateTimeVal);
                    }
                }
            }


            return attendanceDetails;
        }
    }
}